package day03;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Xiao
 * @date 2021/8/19
 */
public class Homework2 {
    /**
     * 2. 创建表，用【JDBC】完成下列需求：
     * 				create table account (
     * 					id int primary key auto_increment,
     * 					name varchar(20), -- 账户名
     * 					money int -- 账户余额
     * 				)
     * 				1. 给表添加三条数据，账户名分别为张三、李四、老王
     * 				2. 将所有账户的金额都修改成1000
     * 				3. 删除账户名为老王的数据
     * 				4. 模拟一次转账操作，用事务管理起来：张三给李四转500元
     */
    public static void main(String[] args) throws Exception{
        String url = "jdbc:mysql:///day03?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement stmt = conn.createStatement();
        //1. 给表添加三条数据，账户名分别为张三、李四、老王
//        method1(stmt, conn);
        //2. 将所有账户的金额都修改成1000
//        method2(stmt, conn);
        //3. 删除账户名为老王的数据
//        method3(stmt, conn);
        //4. 模拟一次转账操作，用事务管理起来：张三给李四转500元
        method4(conn, stmt);
    }

    private static void method4(Connection conn, Statement stmt) throws SQLException {
        try {
            conn.setAutoCommit(false);
            String sql6 = "update account set money = money - 500 where name = '张三'";
            String sql7 = "update account set money = money + 500 where name = '李四'";
            System.out.println(stmt.executeUpdate(sql6));
            System.out.println(stmt.executeUpdate(sql7));
            conn.commit();
        } catch (Exception e) {
            System.out.println("转账失败。。。");
            conn.rollback();
        } finally {
            stmt.close();
            conn.close();
        }
    }

    private static void method3(Statement stmt, Connection conn) throws SQLException {
        String sql5 = "delete from account where name = '老王'";
        System.out.println(stmt.executeUpdate(sql5));
        stmt.close();
        conn.close();
    }

    private static void method2(Statement stmt, Connection conn) throws SQLException {
        String sql4 = "update account set money = 1000";
        System.out.println(stmt.executeUpdate(sql4));
        stmt.close();
        conn.close();
    }

    private static void method1(Statement stmt, Connection conn) throws SQLException {
        String sql1 = "insert into account (name) values ('张三')";
        String sql2 = "insert into account (name) values ('李四')";
        String sql3 = "insert into account (name) values ('老王')";
        int count1 = stmt.executeUpdate(sql1);
        int count2 = stmt.executeUpdate(sql2);
        int count3 = stmt.executeUpdate(sql3);
        System.out.println(count1 + "\r\n" + count2 + "\r\n" + count3);
        stmt.close();
        conn.close();
    }
}
